﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using CP.Entities;
using CP.DBService;
using CP.Utility;
using System.Data;
using Oracle.ManagedDataAccess.Client;

namespace CP.Repositories.GPM
{
    public class WebpartAuthRepository
    {
        DBHelper db = DBHelper.GPMCreateInstance();

        public IList<User_Group> GetUser_Groups(string sql)
        {
            sql = "select * from User_Group where 1=1 " + sql + " order by group_id";
            return db.ExecuteObject<User_Group>(sql);
        }

        public IList<Menu> GetAuthMenus(int groupid)
        {
            string sql = string.Format(@"SELECT *
                      FROM menu t1
                     WHERE EXISTS (
                               SELECT NULL
                                 FROM menu_right t2
                                WHERE t1.menu_id = t2.menu_id AND GROUP_ID = {0}
                                      AND right_flag = 'R')", groupid);
            return db.ExecuteObject<Menu>(sql);
        }

        public IList<Menu> GetUnAuthMenus(int groupid)
        {
            string sql = string.Format(@"SELECT *
                          FROM menu
                         WHERE menu_id IN (
                                       SELECT DISTINCT parent_id
                                                  FROM menu
                                                 WHERE parent_id <> 0
                                                   AND menu_id NOT IN (SELECT menu_id
                                                                         FROM menu_right
                                                                        WHERE GROUP_ID = {0}))
                        UNION
                        SELECT *
                          FROM menu
                         WHERE menu_id NOT IN (SELECT menu_id
                                                 FROM menu_right
                                                WHERE GROUP_ID = {0})", groupid);
            return db.ExecuteObject<Menu>(sql);
        }

        public IList<User_Info> GetUser_Infos(string sql)
        {
            sql = "select * from User_Info where 1=1 " + sql;
            return db.ExecuteObject<User_Info>(sql);
        }

//        public int MenuAuth(int groupid, IList<string> menus)
//        {
//            try
//            {
//                db.BeginTransaction();
//                foreach (string menuid in menus)
//                {
//                    string sql = string.Format(@"SELECT COUNT (*)
//                                                  FROM DUAL
//                                        WHERE 
//                                        EXISTS (SELECT NULL
//                                                FROM menu_right
//                                            WHERE group_id = {0} 
//                                              AND menu_id = {1} 
//                                              AND right_flag = 'R')",
//                                             groupid, menuid);
//                    int exist = Convert.ToInt16(db.ExecuteScalar(sql));
//                    if (0 == exist)
//                    {
//                        sql = string.Format(@"insert into menu_right
//                                                    (group_id,menu_id,right_flag) 
//                                              values({0},{1},'R') ",
//                                                     groupid, menuid);
//                        db.ExecuteNonQuery(sql);
//                    }
//                }
//                db.Commit();
//                return 1;
//            }
//            catch (Exception e)
//            {
//                db.Rollback();
//                throw e;
//            }
//        }

        public int MenuAuth(int groupid, IList<string> menus)
        {
            string errmsg = string.Empty;

            try
            {
                OracleParameter[] parameters ={
                new OracleParameter("p_groupid",OracleDbType.Int32),
                new OracleParameter("p_menus",OracleDbType.Varchar2,4000),
                new OracleParameter("p_errmsg",OracleDbType.Varchar2,200)
　　　　　　　　　　　　　};
                parameters[0].Direction = ParameterDirection.Input;
                parameters[1].Direction = ParameterDirection.Input;
                parameters[2].Direction = ParameterDirection.Output;

                parameters[0].Value = groupid;
                parameters[1].Value = string.Join(",", menus.ToArray());

                db.ExecuteNonQuery("gpm.proc_menu_auth", parameters, CommandType.StoredProcedure);
                errmsg = parameters[2].Value.ToString();
                if (string.IsNullOrEmpty(errmsg))
                    return 1;
                throw new Exception(errmsg);
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public int MenuUnAuth(int groupid, IList<string> menus)
        {
            string errmsg = string.Empty;

            try
            {
                OracleParameter[] parameters ={
                new OracleParameter("p_groupid",OracleDbType.Int32),
                new OracleParameter("p_menus",OracleDbType.Varchar2,4000),
                new OracleParameter("p_errmsg",OracleDbType.Varchar2,200)
　　　　　　　　　　　　　};
                parameters[0].Direction = ParameterDirection.Input;
                parameters[1].Direction = ParameterDirection.Input;
                parameters[2].Direction = ParameterDirection.Output;

                parameters[0].Value = groupid;
                parameters[1].Value = string.Join(",", menus.ToArray());

                db.ExecuteNonQuery("gpm.proc_menu_unauth", parameters, CommandType.StoredProcedure);
                errmsg = parameters[2].Value.ToString();
                if (string.IsNullOrEmpty(errmsg))
                    return 1;
                throw new Exception(errmsg);
            }
            catch (Exception e)
            {
                throw e;
            }
        }

//        public int MenuUnAuth(int groupid, IList<string> menus)
//        {
//            try
//            {
//                db.BeginTransaction();
//                foreach (string menuid in menus)
//                {
//                    string sql = string.Format(@"delete menu_right where
//                                                        group_id= {0}
//                                                    and menu_id = {1}",
//                                                 groupid, menuid);
//                    db.ExecuteNonQuery(sql);
//                }
//                db.Commit();
//                return 1;
//            }
//            catch (Exception e)
//            {
//                db.Rollback();
//                throw e;
//            }
//        }
    }
}
